﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRest_GetRestoreScripts]
#-- Purpose:		Returns a list of scripts to execute to restore a database
#--	Last Update:	02/11/2016
#--					For a complete history - please review comments in SVN
#-- Called By:		Restore Database Utility Executable
#--					PowerShell Scripts
#--					spRest_ByBackupSetID
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRest_GetRestoreScripts]
(
	@database_name			sysname,
	@last_backup_set_id		int,
	@destination_db			sysname				= NULL,
	@stopat_time			datetime			= NULL,
	@skip_pre_backup		bit					= 0,
	@recover_now			bit					= 1,
	@last_file_restored		nvarchar(260)		= NULL,
	@destination_state		tinyint				= 0,
	@destination_is_standby	bit					= 0
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE		@start_date datetime, @database sysname, @sCommand nvarchar(3000)
DECLARE		@db_id int, @db_diff_id int, @last_id int, @db_diff_backupsetid int
DECLARE		@id int, @first_id int, @physical_device_name varchar(4000), @replace bit, @recover bit
DECLARE		@iTotal int, @iCount int, @file_exists bit

--- Parameter Pre-processing
IF LTRIM(RTRIM(@destination_db)) = ''
	SET	@destination_db = NULL
IF @skip_pre_backup IS NULL
	SET	@skip_pre_backup = 0
IF LTRIM(RTRIM(@last_file_restored)) = ''
	SET	@last_file_restored = NULL

--- Destination Database
SELECT	@database = ISNULL(@destination_db, @database_name)

--- Define Temp Tables
DECLARE @scripts TABLE
(
	id							int					IDENTITY(1, 1)  PRIMARY KEY,
	name						sysname,
	command						nvarchar(3000),
	file_exists					bit					DEFAULT (1)
)
DECLARE @backupset TABLE
(
	id							int					PRIMARY KEY,
	recovery_path_broken		bit,
	backup_set_id				int,
	db_backup_set_id			int,
	prev_tlog_backup_set_id		int,
	database_name				nvarchar(128),
	name						nvarchar(128),
	type						char(1),
	backup_start_date			datetime,
	has_bulk_logged_data		bit,
	backup_size					numeric(20,0),
	physical_device_name		nvarchar(260),
	file_exists					bit,
	database_backup_lsn			numeric(25,0),
	differential_base_lsn		numeric(25,0),
	checkpoint_lsn				numeric(25,0),
	fork_point_lsn				numeric(25,0),
	first_lsn					numeric(25,0),
	last_lsn					numeric(25,0),
	first_recovery_fork_guid	uniqueidentifier,
	last_recovery_fork_guid		uniqueidentifier
)

--- Determine the starting date based on the @last_backup_set_id requested
SELECT	@start_date = DATEADD(ss, 1, backup_start_date)
FROM	[msdb].[dbo].[backupset]
WHERE	database_name = @database_name
		AND backup_set_id = @last_backup_set_id

--- Get the full list of backups
INSERT INTO @backupset
	SELECT		id,
				recovery_path_broken,
				backup_set_id,
				db_backup_set_id,
				prev_tlog_backup_set_id,
				database_name,
				name,
				type,
				backup_start_date,
				has_bulk_logged_data,
				backup_size,
				physical_device_name,
				file_exists,
				database_backup_lsn,
				differential_base_lsn,
				checkpoint_lsn,
				fork_point_lsn,
				first_lsn,
				last_lsn,
				first_recovery_fork_guid,
				last_recovery_fork_guid
	FROM	[dbo].[fnBkp_GetAllBackups] (@database_name, DEFAULT, DEFAULT, 0, 0) t1

--- Determine the criteria for selecting the proper backups to restore
SELECT		@db_id = ISNULL(t3.id, t2.id),
			@db_diff_id =  t2.id,
			@last_id = t1.id,
			@db_diff_backupsetid = t2.backup_set_id
FROM		@backupset t1
JOIN		@backupset t2 ON (t1.type <> 'L' AND t1.backup_set_id = t2.backup_set_id)
			OR (t1.type = 'L' AND t1.db_backup_set_id = t2.backup_set_id)
LEFT JOIN	@backupset t3 ON t2.db_backup_set_id = t3.backup_set_id
WHERE		t1.backup_set_id = @last_backup_set_id

--- Remove the backups that are not needed for this restore
DELETE
FROM		@backupset
WHERE		NOT (
				id IN (@db_id, @db_diff_id)
				OR (
					id BETWEEN @db_diff_id AND @last_id
					AND db_backup_set_id = @db_diff_backupsetid
				)
			)
			OR (
				type = 'L'
				AND db_backup_set_id IS NULL
			)

--- Store the first and last id value of the restore process
SELECT	@first_id = (SELECT MIN(id) FROM @backupset),
		@last_id = (SELECT MAX(id) FROM @backupset)

--- If the @last_file_restored is passed in, this is either a logship (@recover_now = 0)
---		or a restart (@recover_now = 1).  In either case, a full restore will occur
---		anyways if any of the following are true:
---			1.  If @last_file_restored is not found in @backupset
---			2.	If the database is not either (1) restoring or (2) online in standby
IF @last_file_restored IS NOT NULL
  BEGIN
	IF EXISTS (SELECT TOP 1 * FROM @backupset WHERE physical_device_name = @last_file_restored)
		AND (
			@destination_state = 1
			OR (@destination_state = 0 AND @destination_is_standby = 1)
			)
	  BEGIN
		DELETE		@backupset
		WHERE		id <= (SELECT id FROM @backupset WHERE physical_device_name = @last_file_restored)

		--- If this is only a partial restore, backups can't happen
		IF @skip_pre_backup = 0
			SET @skip_pre_backup = 1
	  END
  END

------------------------------------------------------
--- Build the list of scripts to be executed
---

--- Remove all snapshots
INSERT INTO @scripts (name, command)
	VALUES ('Drop Snapshots', 'EXEC	[dbo].[spBkp_SnapshotDrop] @database_name = ''' + @database + '''')

--- Take a pre-restore backup
IF @skip_pre_backup = 0
  BEGIN
	SELECT	@sCommand = '--- Pick the shortest possible backup (if Simple Recovery then Diff, else Log)
	IF EXISTS (SELECT TOP 1 [name] FROM [sys].[databases] WHERE [name] = ''' + @database + ''')
	  BEGIN
		DECLARE	@noarch bit
		SELECT	@noarch = [dbo].[fnConfig_IsDatabaseNoArchive] ( ''' + @database + ''')
	'

	IF @database <> @database_name
		SELECT	@sCommand = @sCommand + '
		IF @noarch = 1
			EXEC	[dbo].[spBkp_Differential] @database_name = ''' + @database + ''', @label = ''PRE_Restore_' + @database_name + '_To_' + @destination_db + ''', @verify = 0
		ELSE
			EXEC	[dbo].[spBkp_Log] @database_name = ''' + @database + ''', @label = ''PRE_Restore_' + @database_name + '_To_' + @destination_db + ''', @verify = 0'
	ELSE
		SELECT	@sCommand = @sCommand + '
		IF @noarch = 1
			EXEC	[dbo].[spBkp_Differential] @database_name = ''' + @database + ''', @label = ''PRE_Restore'', @verify = 0
		ELSE
			EXEC	[dbo].[spBkp_Log] @database_name = ''' + @database + ''', @label = ''PRE_Restore'', @verify = 0'

	SELECT	@sCommand = @sCommand + '
	  END'
	INSERT INTO @scripts (name, command)
		VALUES ('Pre-restore backup', @sCommand)
  END

--- Drop existing connections
INSERT INTO @scripts (name, command) VALUES ('Kill open database connections', 'EXEC	[dbo].[spRest_KillProcesses] @database_name = ''' + @database + '''')

--- If the database is in standby mode, it needs to switch back to recovering mode.
IF EXISTS (SELECT	TOP 1 * 
		   FROM		sys.databases 
		   WHERE	name = @database_name 
					AND state = 0 
					AND is_in_standby = 1
		  )
  BEGIN
	INSERT INTO @scripts (name, command)
		VALUES ('Return to NoRecovery Mode', 'RESTORE DATABASE ' + QUOTENAME(@database) + ' WITH NORECOVERY;')
  END

--- Restore the backup file(s)
SELECT		@iCount = 1,
			@iTotal = COUNT(*)
FROM		@backupset

DECLARE crsRestore CURSOR FOR 
	SELECT		id,
				physical_device_name,
				file_exists
	FROM		@backupset
	ORDER BY	id
OPEN crsRestore

--- Get the first restore command and begin the restore process
FETCH NEXT FROM crsRestore INTO @id, @physical_device_name, @file_exists
WHILE @@FETCH_STATUS <> -1
	BEGIN
		--- Modify parameters for the restore command
		SELECT	@replace = (CASE WHEN @id = @first_id THEN 1 ELSE 0 END)

		--- Execute the file restore
		SELECT	@sCommand = 'EXEC	[dbo].[spRest_BackupFile]	
										@database_name = ''' + @database + ''', 
										@file_name = ''' + @physical_device_name + ''',
										@recover_now = 0,
										@replace_existing = ' + CAST(@replace as varchar(10)) + ',
										@verify_only = 0' 
										--, @stopat_time = @stopat_time

		INSERT INTO @scripts (name, command, file_exists)
			VALUES ('Restore backup file ' + CAST(@iCount as varchar(10)) + ' of ' + CAST(@iTotal as varchar(10)), @sCommand, @file_exists)

		--- Move to the next restore command
		SELECT	@iCount = @iCount + 1
		FETCH NEXT FROM crsRestore INTO @id, @physical_device_name, @file_exists
	END
CLOSE crsRestore
DEALLOCATE crsRestore

IF @recover_now = 0
  BEGIN
	IF @destination_is_standby = 1
	  BEGIN
		--- Recover the Database
		SELECT	@sCommand = 'EXEC	[dbo].[spRest_RecoverDatabase] @database_name = ''' + @database + ''', @as_standby = 1'
		INSERT INTO @scripts (name, command)
			VALUES ('Recover the database', @sCommand)
	  END
  END
ELSE
  BEGIN
	--- Recover the Database
	SELECT	@sCommand = 'EXEC	[dbo].[spRest_RecoverDatabase] @database_name = ''' + @database + ''', @as_standby = 0'
	INSERT INTO @scripts (name, command)
		VALUES ('Recover the database', @sCommand)

	--- Remap User IDs
	INSERT INTO @scripts (name, command)
		VALUES ('Remap any users that can be automatically mapped', 'EXEC [dbo].[spRest_ChangeUsersLogin] @database_name = ''' + @database + '''')

	--- Differential Backup
	IF @database <> @database_name
		INSERT INTO @scripts (name, command)
			VALUES ('Post-restore backup', 'EXEC	[dbo].[spBkp_Differential] @database_name = ''' + @database + ''', @label = ''POST_Restore_' + @database_name + '_To_' + @destination_db + ''', @verify = 0')
	ELSE
		INSERT INTO @scripts (name, command)
			VALUES ('Post-restore backup', 'EXEC	[dbo].[spBkp_Differential] @database_name = ''' + @database + ''', @label = ''POST_Restore'', @verify = 0')

	--- Transaction Log Backup
	INSERT INTO @scripts (name, command)
		VALUES ('Transaction log backup', 'EXEC	[dbo].[spBkp_Log] @database_name = ''' + @database + ''', @verify = 0')

	--- ProdCopy Post-restore Script (if exists)
	SELECT @sCommand = 'USE [' + @database + '];
	IF  EXISTS (SELECT object_id FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[spSysUpdateProdCopy]''))
	'
	IF @database <> @database_name
	  BEGIN
		SELECT	@sCommand = @sCommand + '	EXEC dbo.spSysUpdateProdCopy @simple_mode = 1, @shrink_log_file = 1, @shrink_data_file = 1;'
		INSERT INTO @scripts (name, command)
			VALUES ('Execute spSysUpdateProdCopy', @sCommand)
	  END
	ELSE
	  BEGIN
		SELECT	@sCommand = @sCommand + '	EXEC dbo.spSysUpdateProdCopy @simple_mode = 0, @shrink_log_file = 0, @shrink_data_file = 0;'
		INSERT INTO @scripts (name, command)
			VALUES ('Execute spSysUpdateProdCopy (change to Simple + Shrink)', @sCommand)
	  END
  END

--- Ensure only good commands come out of this
DELETE FROM @scripts WHERE command IS NULL

--- Return the scripts to execute
SELECT		id,
			name, 
			command,
			file_exists,
			'' [Result],
			CAST(0 as bit) [Started],
			CAST(1 as bit) [Succeeded],
			CAST(0 as bit) [Processing],
			'' [Status]
FROM		@scripts
ORDER BY	id ASC

SET NOCOUNT OFF